package com.yuda.afterexamscore.controller;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONUtil;
import com.yuda.afterexamscore.domain.DpSubjectScore;
import com.yuda.afterexamscore.domain.SpartaSubject;
import com.yuda.afterexamscore.domain.SystemVariables;
import com.yuda.afterexamscore.service.*;
import com.yuda.afterexamscore.vo.RedisErr;
import com.yuda.afterexamscore.vo.SpartaSubjectVo;
import com.yuda.common.core.utils.DateUtils;
import com.yuda.common.core.utils.bean.BeanUtils;
import com.yuda.common.core.web.domain.AjaxResult;
import com.yuda.common.log.annotation.Log;
import com.yuda.common.log.enums.BusinessType;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cloud.context.config.annotation.RefreshScope;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.time.Duration;
import java.time.Instant;
import java.util.*;
import java.util.concurrent.ConcurrentLinkedQueue;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.stream.Collectors;

/**
 * 计算分数总控Controller
 *
 * @author fmx
 * @date 2021-05-18
 */
@RestController
@RequestMapping("/calculateScore")
@Api("计算分数总Controller")
@RefreshScope
public class CalculateScoreController {
    @Autowired
    private IDpSubjectScoreService dpSubjectScoreService;

    @Autowired
    private IDpQuesScoreService dpQuesScoreService;

    @Autowired
    private IDpTotalScoreService dpTotalScoreService;

    @Autowired
    private ISpartaSubjectService spartaSubjectService;

    @Autowired
    private ITsinghuaFormalAnswerService tsinghuaFormalAnswerService;

    @Autowired
    private  IThQuesScoreMappingService thQuesScoreMappingService;

    @Autowired
    private RedisTemplate redisTemplate;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Value("${reportService}")
    private String reportService;

    @Value("${examUUID}")
    private String examUUID;

    private static Logger log = LoggerFactory.getLogger(CalculateScoreController.class);

    private static String process = "0";

    //四选二科目
    private static String SiXuanErSubject = "1009";

    //四选二题号
    private static String SiXuanErQuesId = "354,355,356,357";

    public static Queue<DpSubjectScore> dpSubjectScoreQueue = new ConcurrentLinkedQueue<DpSubjectScore>();

    @ApiOperation("校招算分，传入学科relation_subject_id，字符串英文逗号分隔,使用前需要确定调整nacos报告服务地址,切记!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
    @Log(title = "insertFromDpSubjectScore", businessType = BusinessType.INSERT)
//    @PreAuthorize(hasRole = "admin")
    @PostMapping("/insertFromDpSubjectScore/xiaozhao/{subjectIds}")
    public void insertFromDpQuesScoreXiaoZhao(@PathVariable String[] subjectIds) throws InterruptedException {
        CalculateScoreController.process = "5";
        //将之前的错误都删除
        redisTemplate.delete(examUUID+"InsertQuesErr");
        //2021年6月9日新增，添加一个开关，让死锁接口只能存在一个
        //如果能同行，关闭开关
        SystemVariables.InterfaceSwitch = false;
        SystemVariables.msg = "生成小题分表和科目表中，请稍等";
        //重新运行重置两张表状态
        for (String subject : subjectIds) {
            redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
            redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
        }
        //查询redis里科目算分状态,为1则算分,否则返回
        for (String subjectId : subjectIds) {
            Object formalAnswer = redisTemplate.opsForHash().get(examUUID + "ProcessOf" + subjectId, "formalAnswer");
            if (formalAnswer.toString().equals("0")){
                insertInsertQuesErr(subjectId+"学科算分未完成,请先算分");
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
        }
        //生成dp_ques_score,dp_subject_score,dp_total_score表
        jdbcTemplate.execute(createTableDpQUesScore);
        jdbcTemplate.execute(createTableDpSubjectScore);
        jdbcTemplate.execute(createTableDpTotalScore);
        //提取subjectId,拼接成id,id,id状态
        StringBuilder stringBuilder = new StringBuilder();
        Arrays.stream(subjectIds).forEach(s -> stringBuilder.append(s+","));
        String subjectIdStr = stringBuilder.toString();
        subjectIdStr = subjectIdStr.substring(0, subjectIdStr.length() - 1);
        try {
            //查询tsinghua_formal_answer里有没有score为null的,多发生于主观题没导入成功
            List<String> isExist = jdbcTemplate.queryForList("select 1 from tsinghua_formal_answer where subject_relation_id in (?) and type in (1,2,3,6) and (score is null or REPLACE(score,' ','')  = '' ) LIMIT 0,1", new Object[]{subjectIdStr}, String.class);
            if (CollectionUtil.isNotEmpty(isExist)){
                insertInsertQuesErr(subjectIdStr+"学科,tsinghua_formal_answer有分数为空");
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
        }catch (Exception e){
            insertInsertQuesErr("未知错误，请查看日志");
            CalculateScoreController.process = "0";
            SystemVariables.InterfaceSwitch = true;
            e.printStackTrace();
        }

        //计算时间:初始时间
        Instant insOne = Instant.now();
        //开辟线程池
        int threadNum = subjectIds.length;
        ExecutorService executor = Executors.newFixedThreadPool(10);
        //插入dp_ques_score,插入前先删除-----------------------------------------------------------------------------------
        //删除
        for (String subjectId : subjectIds) {
            int ii = dpQuesScoreService.deleteDpQuesScoreBySubjectId(subjectId);
        }
        //改变进度
        CalculateScoreController.process = "20";
        //多线程插入dp_ques_score
        CountDownLatch countDownLatch2 = new CountDownLatch(threadNum);
        if (threadNum>0){
            for (String spartaSubject : subjectIds) {
               // Runnable task = new CalculateScoreController.MultithreadingOfInsertFromFormalAnswerSiXuanEr(countDownLatch2,spartaSubject);
                Runnable task = new CalculateScoreController.MultithreadingOfInsertFromFormalAnswer(countDownLatch2,spartaSubject);
                // 执行
                executor.execute(task);
            }
        }
        countDownLatch2.await();
        //检查dp_ques_score表和tsinghua_formal_answer表数据个数是否相同,只比较选择填空
        //todo 904有4选2先去掉下次加回来
//        try {
//            //TODO 四选二的情况没有考虑
//            String tfaC = jdbcTemplate.queryForObject("SELECT count(1) c from tsinghua_formal_answer where subject_relation_id in ("+subjectIdStr+") and type in (1,2,3,6)", String.class);
//            String dqsC = jdbcTemplate.queryForObject("SELECT count(1) c from dp_ques_score where subject_relation_id in ("+subjectIdStr+") and type in (1,2,3,6)", String.class);
//            if (!tfaC.equals(dqsC)){
//                insertInsertQuesErr("tsinghua_formal_answer与dp_ques_score数据数量不一致");
//                for (String subject : subjectIds) {
//                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
//                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
//                }
//                CalculateScoreController.process = "0";
//                SystemVariables.InterfaceSwitch = true;
//                return;
//            }
//            //改变进度
//            CalculateScoreController.process = "50";
//        }catch (Exception e){
//            insertInsertQuesErr("未知错误，请查看日志");
//            CalculateScoreController.process = "0";
//            SystemVariables.InterfaceSwitch = true;
//            e.printStackTrace();
//        }
        //将该科目的redis算分状态改变
        for (String subjectId : subjectIds) {
            redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subjectId,"quesScore","1");
        }
        //插入dp_subject_score,插入前先删除-----------------------------------------------------------------------------------
        for (String subjectId : subjectIds) {
            int iii = dpSubjectScoreService.deleteDpSubjectScoreBySubjectId(subjectId);
        }
        //改变进度
        CalculateScoreController.process = "70";
        CountDownLatch countDownLatch3 = new CountDownLatch(threadNum);
        if (threadNum>0){
            for (String spartaSubject : subjectIds) {
                Runnable task = new CalculateScoreController.MultithreadingOfInsertFromDpQuesScore(countDownLatch3,spartaSubject);
                // 执行
                executor.execute(task);
            }
        }
        countDownLatch3.await();
        //检查dp_subject_score数据是否相同
        try {
            String dqsC2 = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT 1 from dp_ques_score where subject_relation_id in ("+subjectIdStr+") GROUP BY student_id,subject_relation_id ) a", String.class);
            String dscC = jdbcTemplate.queryForObject("SELECT count(1) c from dp_subject_score where subject_relation_id in ("+subjectIdStr+")", String.class);
            if (!dqsC2.equals(dscC)){
                insertInsertQuesErr("dp_ques_score group by数量与dp_subject_score数据数量不一致");
                for (String subject : subjectIds) {
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
                }
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
            for (String subjectId : subjectIds) {
                redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subjectId,"subjectScore","1");
            }
            //检查单科总分是否超过总分上线--2021年6月5日新增
            List<String> dpSubjectScoreVos = jdbcTemplate.queryForList("SELECT count(1) count from \n" +
                    "dp_subject_score a \n" +
                    "LEFT JOIN sparta_subject b on a.subject_relation_id = b.relation_subject_id  and b.relation_subject_id not like '%#%'  \n" +
                    "where a.score > b.full_score and subject_relation_id in (" + subjectIdStr + ")", String.class);
            if (!dpSubjectScoreVos.get(0).equals("0")){
                insertInsertQuesErr(subjectIdStr+"学科,有学生单科总分超过该科总分上线，总数为："+dpSubjectScoreVos.get(0));
                for (String subject : subjectIds) {
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
                }
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
            //改变进度
            CalculateScoreController.process = "80";
        }catch (Exception e){
            insertInsertQuesErr("未知错误，请查看日志");
            CalculateScoreController.process = "0";
            SystemVariables.InterfaceSwitch = true;
            e.printStackTrace();
        }
        //创建单线程池
//        ExecutorService singleExecutor = Executors.newSingleThreadExecutor();
//        CountDownLatch latch4 = new CountDownLatch(10);
//        Runnable task11 = new AutomaticProcessing(latch4, "http://"+reportService+"/task/dpSubjectScore_country2020");
//        Runnable task12 = new AutomaticProcessing(latch4, "http://"+reportService+"/task/dpSubjectScore_province2020");
//        Runnable task13 = new AutomaticProcessing(latch4, "http://"+reportService+"/task/dpSubjectScore_country_jiashi");
//        Runnable task14 = new AutomaticProcessing(latch4, "http://"+reportService+"/task/dpSubjectScore_province_jiashi");
//        Runnable task16 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankx/subjectScore_feigaoge");
//        Runnable task17 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankx/subjectScore_gaoge");
//        Runnable task18 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankx/subjectScore_jiashi");
//        Runnable task20 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankP/subjectRank_feigaoge");
//        Runnable task21 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankP/subjectRank_gaoge");
//        Runnable task22 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankP/subjectRank_jiashi");
//        singleExecutor.execute(task11);
//        singleExecutor.execute(task12);
//        singleExecutor.execute(task13);
//        singleExecutor.execute(task14);
//        singleExecutor.execute(task16);
//        singleExecutor.execute(task17);
//        singleExecutor.execute(task18);
//        singleExecutor.execute(task20);
//        singleExecutor.execute(task21);
//        singleExecutor.execute(task22);
//        latch4.await();
        //计算时间:结束时间
        Instant insTwo = Instant.now();
        executor.shutdown();//关闭线程池
//        singleExecutor.shutdown();
        CalculateScoreController.process = "100";
        //最后打开开关
        SystemVariables.InterfaceSwitch = true;
        System.out.println("Difference in seconds(秒) : " + Duration.between(insOne, insTwo).getSeconds());
    }

    @ApiOperation("标考算分，传入学科relation_subject_id，字符串英文逗号分隔,使用前需要确定调整nacos报告服务地址,切记!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
    @Log(title = "insertFromDpSubjectScore", businessType = BusinessType.INSERT)
//    @PreAuthorize(hasRole = "admin")
    @PostMapping("/insertFromDpSubjectScore/biaokao/{subjectIds}")
    public void insertFromDpQuesScoreBiaokao(@PathVariable String[] subjectIds) throws InterruptedException {
        CalculateScoreController.process = "5";
        //将之前的错误都删除
        redisTemplate.delete(examUUID+"InsertQuesErr");
        //2021年6月9日新增，添加一个开关，让死锁接口只能存在一个
        //如果能同行，关闭开关
        SystemVariables.InterfaceSwitch = false;
        SystemVariables.msg = "生成小题分表和科目表中，请稍等";
        //重新运行重置两张表状态
        for (String subject : subjectIds) {
//            redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
            redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
        }
        //查询redis里小题分状态,为1则算分,否则返回
        for (String subjectId : subjectIds) {
            Object formalAnswer = redisTemplate.opsForHash().get(examUUID + "ProcessOf" + subjectId, "quesScore");
            if (formalAnswer.toString().equals("0")){
                insertInsertQuesErr(subjectId+"小题分未导入,请先导入小题分");
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
        }
        //提取subjectId,拼接成id,id,id状态
        StringBuilder stringBuilder = new StringBuilder();
        Arrays.stream(subjectIds).forEach(s -> stringBuilder.append(s+","));
        String subjectIdStr = stringBuilder.toString();
        subjectIdStr = subjectIdStr.substring(0, subjectIdStr.length() - 1);
//        try {
//            //查询tsinghua_formal_answer里有没有score为null的,多发生于主观题没导入成功
//            List<String> isExist = jdbcTemplate.queryForList("select 1 from tsinghua_formal_answer where subject_relation_id in (?) and type in (1,2,3,6) and (score is null or REPLACE(score,' ','')  = '' ) LIMIT 0,1", new Object[]{subjectIdStr}, String.class);
//            if (CollectionUtil.isNotEmpty(isExist)){
//                insertInsertQuesErr(subjectIdStr+"学科,tsinghua_formal_answer有分数为空");
//                CalculateScoreController.process = "0";
//                SystemVariables.InterfaceSwitch = true;
//                return;
//            }
//        }catch (Exception e){
//            insertInsertQuesErr("未知错误，请查看日志");
//            CalculateScoreController.process = "0";
//            SystemVariables.InterfaceSwitch = true;
//            e.printStackTrace();
//        }

        //计算时间:初始时间
        Instant insOne = Instant.now();
        //开辟线程池
        int threadNum = subjectIds.length;
        ExecutorService executor = Executors.newFixedThreadPool(10);
//        //插入dp_ques_score,插入前先删除-----------------------------------------------------------------------------------
//        //删除
//        for (String subjectId : subjectIds) {
//            int ii = dpQuesScoreService.deleteDpQuesScoreBySubjectId(subjectId);
//        }
//        //改变进度
//        CalculateScoreController.process = "20";
//        //多线程插入dp_ques_score
//        CountDownLatch countDownLatch2 = new CountDownLatch(threadNum);
//        if (threadNum>0){
//            for (String spartaSubject : subjectIds) {
//                Runnable task = new CalculateScoreController.MultithreadingOfInsertFromFormalAnswer(countDownLatch2,spartaSubject);
//                // 执行
//                executor.execute(task);
//            }
//        }
//        countDownLatch2.await();
        //检查dp_ques_score表和tsinghua_formal_answer表数据个数是否相同,只比较选择填空
//        try {
//            //TODO 四选二的情况没有考虑
//            String tfaC = jdbcTemplate.queryForObject("SELECT count(1) c from tsinghua_formal_answer where subject_relation_id in ("+subjectIdStr+") and type in (1,2,3,6)", String.class);
//            String dqsC = jdbcTemplate.queryForObject("SELECT count(1) c from dp_ques_score where subject_relation_id in ("+subjectIdStr+") and type in (1,2,3,6)", String.class);
//            if (!tfaC.equals(dqsC)){
//                insertInsertQuesErr("tsinghua_formal_answer与dp_ques_score数据数量不一致");
//                for (String subject : subjectIds) {
//                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
//                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
//                }
//                CalculateScoreController.process = "0";
//                SystemVariables.InterfaceSwitch = true;
//                return;
//            }
//            //改变进度
//            CalculateScoreController.process = "50";
//        }catch (Exception e){
//            insertInsertQuesErr("未知错误，请查看日志");
//            CalculateScoreController.process = "0";
//            SystemVariables.InterfaceSwitch = true;
//            e.printStackTrace();
//        }
//        //将该科目的redis算分状态改变
//        for (String subjectId : subjectIds) {
//            redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subjectId,"quesScore","1");
//        }
        //插入dp_subject_score,插入前先删除-----------------------------------------------------------------------------------
        for (String subjectId : subjectIds) {
            int iii = dpSubjectScoreService.deleteDpSubjectScoreBySubjectId(subjectId);
        }
        //改变进度
        CalculateScoreController.process = "70";
        CountDownLatch countDownLatch3 = new CountDownLatch(threadNum);
        if (threadNum>0){
            for (String spartaSubject : subjectIds) {
                Runnable task = new CalculateScoreController.MultithreadingOfInsertFromDpQuesScore(countDownLatch3,spartaSubject);
                // 执行
                executor.execute(task);
            }
        }
        countDownLatch3.await();
        //检查dp_subject_score数据是否相同
        try {
            //String dqsC2 = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT 1 from dp_ques_score where subject_relation_id in ("+subjectIdStr+") GROUP BY student_id,subject_relation_id ) a", String.class);
            String dqsC2 = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT 1 from dp_ques_score where subject_relation_id in ("+subjectIdStr+") GROUP BY zkz_num,subject_relation_id ) a", String.class);
            String dscC = jdbcTemplate.queryForObject("SELECT count(1) c from dp_subject_score where subject_relation_id in ("+subjectIdStr+")", String.class);
            if (!dqsC2.equals(dscC)){
                insertInsertQuesErr("dp_ques_score group by数量与dp_subject_score数据数量不一致");
                for (String subject : subjectIds) {
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
                }
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
            for (String subjectId : subjectIds) {
                redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subjectId,"subjectScore","1");
            }
            //检查单科总分是否超过总分上线--2021年6月5日新增
            List<String> dpSubjectScoreVos = jdbcTemplate.queryForList("SELECT count(1) count from \n" +
                    "dp_subject_score a \n" +
                    "LEFT JOIN sparta_subject b on a.subject_relation_id = b.relation_subject_id  and b.relation_subject_id not like '%#%'  \n" +
                    "where a.score > b.full_score and subject_relation_id in (" + subjectIdStr + ")", String.class);
            if (!dpSubjectScoreVos.get(0).equals("0")){
                insertInsertQuesErr(subjectIdStr+"学科,有学生单科总分超过该科总分上线，总数为："+dpSubjectScoreVos.get(0));
                for (String subject : subjectIds) {
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
                }
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
            //改变进度
            CalculateScoreController.process = "80";
        }catch (Exception e){
            insertInsertQuesErr("未知错误，请查看日志");
            CalculateScoreController.process = "0";
            SystemVariables.InterfaceSwitch = true;
            e.printStackTrace();
        }
        //创建单线程池
//        ExecutorService singleExecutor = Executors.newSingleThreadExecutor();
//        CountDownLatch latch4 = new CountDownLatch(10);
//        Runnable task11 = new AutomaticProcessing(latch4, "http://"+reportService+"/task/dpSubjectScore_country2020");
//        Runnable task12 = new AutomaticProcessing(latch4, "http://"+reportService+"/task/dpSubjectScore_province2020");
//        Runnable task13 = new AutomaticProcessing(latch4, "http://"+reportService+"/task/dpSubjectScore_country_jiashi");
//        Runnable task14 = new AutomaticProcessing(latch4, "http://"+reportService+"/task/dpSubjectScore_province_jiashi");
//        Runnable task16 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankx/subjectScore_feigaoge");
//        Runnable task17 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankx/subjectScore_gaoge");
//        Runnable task18 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankx/subjectScore_jiashi");
//        Runnable task20 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankP/subjectRank_feigaoge");
//        Runnable task21 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankP/subjectRank_gaoge");
//        Runnable task22 = new AutomaticProcessing(latch4, "http://"+reportService+"/rankP/subjectRank_jiashi");
//        singleExecutor.execute(task11);
//        singleExecutor.execute(task12);
//        singleExecutor.execute(task13);
//        singleExecutor.execute(task14);
//        singleExecutor.execute(task16);
//        singleExecutor.execute(task17);
//        singleExecutor.execute(task18);
//        singleExecutor.execute(task20);
//        singleExecutor.execute(task21);
//        singleExecutor.execute(task22);
//        latch4.await();
//        //计算时间:结束时间
//        Instant insTwo = Instant.now();
//        executor.shutdown();//关闭线程池
//        singleExecutor.shutdown();
                //计算时间:结束时间
        Instant insTwo = Instant.now();
        CalculateScoreController.process = "100";
        //最后打开开关
        SystemVariables.InterfaceSwitch = true;
        System.out.println("Difference in seconds(秒) : " + Duration.between(insOne, insTwo).getSeconds());
    }




    @ApiOperation("标考算分，算出考试的所有科目，字符串英文逗号分隔,使用前需要确定调整nacos报告服务地址,切记!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!")
    @Log(title = "insertFromDpSubjectScore", businessType = BusinessType.INSERT)
//    @PreAuthorize(hasRole = "admin")
    @PostMapping("/insertFromDpSubjectScore/biaokao/allSubject")
    public void insertFromDpQuesScoreBiaokaoAll() throws InterruptedException {
        //将之前的错误都删除
        redisTemplate.delete(examUUID+"InsertQuesErr");
        //2021年6月9日新增，添加一个开关，让死锁接口只能存在一个
        //如果能同行，关闭开关
        SystemVariables.InterfaceSwitch = false;
        SystemVariables.msg = "生成小题分表和科目表中，请稍等";

        // 查询dp_ques_score中所有的科目
        String sql = "select distinct subject_relation_id from dp_ques_score where  subject_relation_id not like '%#%'";

        List<String> subjectIds =jdbcTemplate.queryForList(sql,String.class);


        //重新运行重置两张表状态
        for (String subject : subjectIds) {
//            redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
            redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
        }
        //查询redis里小题分状态,为1则算分,否则返回
        for (String subjectId : subjectIds) {
            Object formalAnswer = redisTemplate.opsForHash().get(examUUID + "ProcessOf" + subjectId, "quesScore");
            if (formalAnswer.toString().equals("0")){
                insertInsertQuesErr(subjectId+"小题分未导入,请先导入小题分");
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
        }
        //提取subjectId,拼接成id,id,id状态
        StringBuilder stringBuilder = new StringBuilder();
        subjectIds.forEach(s -> stringBuilder.append(s+","));
        String subjectIdStr = stringBuilder.toString();
        subjectIdStr = subjectIdStr.substring(0, subjectIdStr.length() - 1);

        //计算时间:初始时间
        Instant insOne = Instant.now();
        //开辟线程池
        int threadNum = subjectIds.size();
        ExecutorService executor = Executors.newFixedThreadPool(10);

        //检查dp_ques_score表和tsinghua_formal_answer表数据个数是否相同,只比较选择填空

        //插入dp_subject_score,插入前先删除-----------------------------------------------------------------------------------
        for (String subjectId : subjectIds) {
            int iii = dpSubjectScoreService.deleteDpSubjectScoreBySubjectId(subjectId);
        }
        //改变进度
        CalculateScoreController.process = "70";
        CountDownLatch countDownLatch3 = new CountDownLatch(threadNum);
        if (threadNum>0){
            for (String spartaSubject : subjectIds) {
                Runnable task = new CalculateScoreController.MultithreadingOfInsertFromDpQuesScore(countDownLatch3,spartaSubject);
                // 执行
                executor.execute(task);
            }
        }
        countDownLatch3.await();
        //检查dp_subject_score数据是否相同
        try {
            //String dqsC2 = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT 1 from dp_ques_score where subject_relation_id in ("+subjectIdStr+") GROUP BY student_id,subject_relation_id ) a", String.class);
            String dqsC2 = jdbcTemplate.queryForObject("SELECT count(1) c from (SELECT 1 from dp_ques_score where subject_relation_id in ("+subjectIdStr+") GROUP BY zkz_num,subject_relation_id ) a", String.class);
            String dscC = jdbcTemplate.queryForObject("SELECT count(1) c from dp_subject_score where subject_relation_id in ("+subjectIdStr+")", String.class);
            if (!dqsC2.equals(dscC)){
                insertInsertQuesErr("dp_ques_score group by数量与dp_subject_score数据数量不一致");
                for (String subject : subjectIds) {
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
                }
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
            for (String subjectId : subjectIds) {
                redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subjectId,"subjectScore","1");
            }
            //检查单科总分是否超过总分上线--2021年6月5日新增
            List<String> dpSubjectScoreVos = jdbcTemplate.queryForList("SELECT count(1) count from \n" +
                    "dp_subject_score a \n" +
                    "LEFT JOIN sparta_subject b on a.subject_relation_id = b.relation_subject_id  and b.relation_subject_id not like '%#%'  \n" +
                    "where a.score > b.full_score and subject_relation_id in (" + subjectIdStr + ")", String.class);
            if (!dpSubjectScoreVos.get(0).equals("0")){
                insertInsertQuesErr(subjectIdStr+"学科,有学生单科总分超过该科总分上线，总数为："+dpSubjectScoreVos.get(0));
                for (String subject : subjectIds) {
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
                    redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
                }
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                return;
            }
            //改变进度
            CalculateScoreController.process = "80";
        }catch (Exception e){
            insertInsertQuesErr("未知错误，请查看日志");
            CalculateScoreController.process = "0";
            SystemVariables.InterfaceSwitch = true;
            e.printStackTrace();
        }
        //计算时间:结束时间
        Instant insTwo = Instant.now();
        CalculateScoreController.process = "100";
        //最后打开开关
        SystemVariables.InterfaceSwitch = true;
        System.out.println("Difference in seconds(秒) : " + Duration.between(insOne, insTwo).getSeconds());
    }

    @ApiOperation("获得各科目算分状态，第一次获取会慢一点")
    @GetMapping("/queryCalculateScoreStatus")
//    @PreAuthorize(hasRole = "admin")
    public AjaxResult queryCalculateScoreStatus() throws InterruptedException {
        //判断该次考试是否存在redis，有，则获取学科状态返回，没有则创建后返回
        Boolean examFlag = redisTemplate.hasKey("fmx"+examUUID);
        List<SpartaSubject> spartaSubjects = spartaSubjectService.selectSpartaSubjectList(null);
        if (!examFlag){
            redisTemplate.opsForValue().set("fmx"+examUUID, DateUtils.dateTimeNow());
            //排除文综理综获取relation_subject_id
            List<String> subjects = spartaSubjects.stream()
                    .filter(s -> !s.getRelationSubjectId().contains("1011") && !s.getRelationSubjectId().contains("1012") && !s.getRelationSubjectId().contains("2011") && !s.getRelationSubjectId().contains("2012"))
                    .map(s -> s.getRelationSubjectId()).collect(Collectors.toList());
            //遍历subjects存入redis
            for (String subject : subjects) {
                redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"formalAnswer","0");
                redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"quesScore","0");
                redisTemplate.opsForHash().put(examUUID+"ProcessOf"+subject,"subjectScore","0");
            }
        }
        //获取所有科目
        List<SpartaSubject> collect = spartaSubjects.stream()
                .filter(s -> !s.getRelationSubjectId().contains("1011") && !s.getRelationSubjectId().contains("1012") && !s.getRelationSubjectId().contains("2011") && !s.getRelationSubjectId().contains("2012"))
                .collect(Collectors.toList());
        List<SpartaSubjectVo> subjectVos =new ArrayList<>();
        //在redis里找状态
        for (SpartaSubject spartaSubject : collect) {
            SpartaSubjectVo spartaSubjectVo = new SpartaSubjectVo();
            BeanUtils.copyBeanProp(spartaSubjectVo,spartaSubject);
            spartaSubjectVo.setProcessOfFormalAnswer(redisTemplate.opsForHash().get(examUUID+"ProcessOf"+spartaSubject.getRelationSubjectId(),"formalAnswer").toString());
            spartaSubjectVo.setProcessOfQuesScore(redisTemplate.opsForHash().get(examUUID+"ProcessOf"+spartaSubject.getRelationSubjectId(),"quesScore").toString());
            spartaSubjectVo.setProcessOfSubjectScore(redisTemplate.opsForHash().get(examUUID+"ProcessOf"+spartaSubject.getRelationSubjectId(),"subjectScore").toString());
            subjectVos.add(spartaSubjectVo);
        }
        return AjaxResult.success(subjectVos);
    }

    @ApiOperation("存总分移除科目,用','(英文逗号)分割科目,传字符串,如'1011,1012',如果想删除传null")
    @PostMapping("/saveRemoveSubject/{subjectIds}")
//    @PreAuthorize(hasRole = "admin")
    public AjaxResult saveRemoveSubject(@PathVariable("subjectIds") String subjectIds) {
        //先清空
        dpTotalScoreService.truncateTable();
        if (subjectIds.equals("12321")){
            redisTemplate.delete(examUUID + "RemoveSubject");
        }else {
            redisTemplate.opsForValue().set(examUUID + "RemoveSubject",subjectIds);
        }
        return AjaxResult.success();
    }

    @ApiOperation("查询移除科目")
    @GetMapping("/queryRemoveSubject")
//    @PreAuthorize(hasRole = "admin")
    public AjaxResult queryRemoveSubject() {
        Boolean hasKey = redisTemplate.hasKey(examUUID + "RemoveSubject");
        if (hasKey){
            Object o = redisTemplate.opsForValue().get(examUUID + "RemoveSubject");
            //从数据库查询科目名称
            String[] subjects = o.toString().split(",");
            StringBuilder builder = new StringBuilder();
            for (String subject : subjects) {
                String subjectName = spartaSubjectService.selectSpartaSubjectByRelationSubjectId(subject);
                builder.append(subjectName+" ");
            }
            return AjaxResult.success(builder.toString());
        }else {
            return AjaxResult.success("暂无去除科目");
        }
    }

    @ApiOperation("获取插入两张表进度")
    @GetMapping("/queryinsertFromDpSubjectScoreProcess")
//    @PreAuthorize(hasRole = "admin")
    public AjaxResult queryinsertFromDpSubjectScoreProcess() {
        return AjaxResult.success(CalculateScoreController.process);
    }

    /**
     * 返回TsinghuaFormalAnswerErr
     *
     */
    @GetMapping("/queryInsertQuesErr")
    @ApiOperation("查询生成表错误,有错误则返回错误列表,没错误则不返回data")
//    @PreAuthorize(hasRole = "admin")
    public AjaxResult queryInsertQuesErr() {
        Boolean hasKey = redisTemplate.hasKey(examUUID + "InsertQuesErr");
        if (hasKey){
            Set members = redisTemplate.opsForSet().members(examUUID + "InsertQuesErr");
            return AjaxResult.success(members);
        }else {
            return AjaxResult.success();
        }
    }

    /**
     * 返回InsertQuesErrErr
     *
     */
    public void insertQuesErr(String msg) {
        RedisErr redisErr = new RedisErr();
        redisErr.setMsg(msg);
        redisErr.setCurrentTime(DateUtil.now());
        JSON parse = JSONUtil.parse(redisErr);
        redisTemplate.opsForSet().add(examUUID+"InsertQuesErr",parse);
    }


    //---------------------------------------------------分割线--------------------------------------------------------------------------------------------------

    /**
     * 多线程之计算score
     *
     */
    private class MultithreadingOfCalculationScore implements Runnable {
        private CountDownLatch threadsSignal;
        private String subjectId;
        public MultithreadingOfCalculationScore(CountDownLatch threadsSignal,String subjectId) {
            this.threadsSignal = threadsSignal;
            this.subjectId = subjectId;
        }

        @Override
        public void run() {
            try {
                thQuesScoreMappingService.calculationScore(subjectId);
                // 线程结束时计数器减1
                threadsSignal.countDown();//必须等核心处理逻辑处理完成后才可以减1
                log.info(Thread.currentThread().getName() + "结束. 还有"+ threadsSignal.getCount() + " 个线程 ");
            }catch (Exception e){
                insertInsertQuesErr("未知错误，请查看日志");
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                e.printStackTrace();
            }
        }
    }

    /**
     * 多线程之插入dp_ques_score
     *
     */
    private class MultithreadingOfInsertFromFormalAnswer implements Runnable {
        private CountDownLatch threadsSignal;
        private String subjectId;
        public MultithreadingOfInsertFromFormalAnswer(CountDownLatch threadsSignal,String subjectId) {
            this.threadsSignal = threadsSignal;
            this.subjectId = subjectId;
        }

        @Override
        public void run() {
            try {
                int i = dpQuesScoreService.insertFromFormalAnswer(subjectId);
                // 线程结束时计数器减1
                threadsSignal.countDown();//必须等核心处理逻辑处理完成后才可以减1
            }catch (Exception e){
                insertInsertQuesErr("未知错误，请查看日志");
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                e.printStackTrace();
            }
        }
    }

    /**
     * 多线程之插入dp_ques_score有四选二版本
     * todo 记得换回来
     */
    private class MultithreadingOfInsertFromFormalAnswerSiXuanEr implements Runnable {
        private CountDownLatch threadsSignal;
        private String subjectId;
        public MultithreadingOfInsertFromFormalAnswerSiXuanEr(CountDownLatch threadsSignal,String subjectId) {
            this.threadsSignal = threadsSignal;
            this.subjectId = subjectId;
        }

        @Override
        public void run() {
            try {
                //todo 记得换科目
                if (this.subjectId.equals(SiXuanErSubject)){
                    //TODO Sql还是得改！！！
                    dpQuesScoreService.insertFromFormalAnswerSiXuanEr(subjectId,SiXuanErQuesId);
                }else {
                    dpQuesScoreService.insertFromFormalAnswer(subjectId);
                }
                // 线程结束时计数器减1
                threadsSignal.countDown();//必须等核心处理逻辑处理完成后才可以减1
            }catch (Exception e){
                insertInsertQuesErr("未知错误，请查看日志");
                CalculateScoreController.process = "0";
                SystemVariables.InterfaceSwitch = true;
                e.printStackTrace();
            }
        }
    }

    /**
     * 多线程之插入dp_subject_score
     *
     */
    private class MultithreadingOfInsertFromDpQuesScore implements Runnable {
        private CountDownLatch threadsSignal;
        private String subjectId;
        public MultithreadingOfInsertFromDpQuesScore(CountDownLatch threadsSignal,String subjectId) {
            this.threadsSignal = threadsSignal;
            this.subjectId = subjectId;
        }

        @Override
        public void run() {
            int i = dpSubjectScoreService.insertFromDpQuesScore(subjectId);
            // 线程结束时计数器减1
            threadsSignal.countDown();//必须等核心处理逻辑处理完成后才可以减1
        }
    }

    class AutomaticProcessing implements Runnable{
        private CountDownLatch threadsSignal;
        private String url;

        public AutomaticProcessing(CountDownLatch threadsSignal, String url) {
            this.threadsSignal = threadsSignal;
            this.url = url;
        }

        @Override
        public void run() {
            try {
                connectPOST(url);
            } catch (IOException e) {
                System.out.println("发送 POST 请求出现异常！" + e);
                e.printStackTrace();
            }
            threadsSignal.countDown();//必须等核心处理逻辑处理完成后才可以减1
            log.info(Thread.currentThread().getName() + "结束. 还有"+ threadsSignal.getCount() + " 个线程 ");
        }

        public String connectPOST(String urlStr) throws IOException {
            InputStream ins = null;
            try {
                URL url = new URL(urlStr);
                HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                conn.setRequestMethod("POST");//POST
                conn.setConnectTimeout(1800000000);
                conn.setDoInput(true);
                conn.setDoOutput(true);
                BufferedWriter ous = new BufferedWriter(new OutputStreamWriter(conn.getOutputStream(), "utf-8"));
                //ous.writeChars(params);
                ous.flush();
                conn.connect();
                int code = conn.getResponseCode();
                if (code == HttpURLConnection.HTTP_OK || code == 400) {
                    ins = conn.getInputStream();
                    return readInputStream(ins);
                }
            } catch (MalformedURLException e) {
                e.printStackTrace();
            } finally {
                if (ins != null) {
                    ins.close();
                }
            }
            return null;
        }

        public String readInputStream(InputStream ins) {
            if (ins == null) {
                return null;
            }
            BufferedInputStream bis = new BufferedInputStream(ins);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            try {
                byte[] buffer = new byte[128];
                int n = -1;
                while ((n = bis.read(buffer)) != -1) {
                    bos.write(buffer, 0, n);
                }
            } catch (IOException e) {
                e.printStackTrace();
                return null;
            } finally {
                if (bis != null) {
                    try {
                        bis.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
            return new String(bos.toByteArray());
        }
    }

    /**
     * insertInsertQuesErr
     *
     */
    public void insertInsertQuesErr(String msg) {
        RedisErr redisErr = new RedisErr();
        redisErr.setMsg(msg);
        redisErr.setCurrentTime(DateUtil.now());
        JSON parse = JSONUtil.parse(redisErr);
        redisTemplate.opsForSet().add(examUUID+"InsertQuesErr",parse);
    }


    private static final String  createTableDpQUesScore = "CREATE TABLE IF NOT EXISTS `dp_ques_score` (\n" +
            "  `id` int(10) NOT NULL AUTO_INCREMENT,\n" +
            "  `zkz_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '准考证(12)位',\n" +
            "  `sfz_num` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '身份证',\n" +
            "  `student_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生ID',\n" +
            "  `student_type` tinyint(2) DEFAULT NULL COMMENT '1线上  2线下',\n" +
            "  `pg_type` tinyint(5) DEFAULT NULL COMMENT 'pg_id',\n" +
            "  `gaige` tinyint(1) DEFAULT NULL,\n" +
            "  `province_id` int(11) DEFAULT NULL COMMENT '省ID',\n" +
            "  `city_id` int(11) DEFAULT NULL,\n" +
            "  `school_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学校ID',\n" +
            "  `banji_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '班级名称',\n" +
            "  `subject_id` int(5) DEFAULT NULL COMMENT '大科目ID',\n" +
            "  `subject_relation_id` int(5) DEFAULT NULL COMMENT '小科目ID(共17科单科，6科总科)',\n" +
            "  `mapping_subject_id` int(5) DEFAULT NULL,\n" +
            "  `ques_id` int(5) DEFAULT NULL COMMENT '题目ID',\n" +
            "  `ques_index` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '题目序号',\n" +
            "  `answer` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '答案',\n" +
            "  `score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分数',\n" +
            "  `type` tinyint(2) DEFAULT NULL COMMENT '1 一卷   2 二卷',\n" +
            "  `sum_score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `full_score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `answer2` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  PRIMARY KEY (`id`) USING BTREE,\n" +
            "  KEY `zkz_num` (`zkz_num`) USING BTREE,\n" +
            "  KEY `ques_index` (`ques_index`) USING BTREE,\n" +
            "  KEY `ques_id` (`ques_id`) USING BTREE,\n" +
            "  KEY `student_id` (`student_id`) USING BTREE,\n" +
            "  KEY `subject_relation_id` (`subject_relation_id`) USING BTREE,\n" +
            "  KEY `student_id_2` (`student_id`,`subject_relation_id`,`ques_id`) USING BTREE,\n" +
            "  KEY `student_id_3` (`student_id`,`subject_relation_id`) USING BTREE,\n" +
            "  KEY `student_id_4` (`student_id`,`subject_id`) USING BTREE,\n" +
            "  KEY `subject_relation_id_2` (`subject_relation_id`,`ques_id`) USING BTREE,\n" +
            "  KEY `score` (`score`) USING BTREE,\n" +
            "  KEY `mapping_subject_id` (`mapping_subject_id`) USING BTREE,\n" +
            "  KEY `full_score` (`full_score`) USING BTREE,\n" +
            "  KEY `gaige` (`gaige`) USING BTREE,\n" +
            "  KEY `student_type` (`student_type`) USING BTREE,\n" +
            "  KEY `zkz_num_2` (`zkz_num`,`mapping_subject_id`) USING BTREE,\n" +
            "  KEY `student_id_5` (`student_id`,`ques_id`,`score`) USING BTREE,\n" +
            "  KEY `student_id_6` (`student_id`,`ques_id`) USING BTREE,\n" +
            "  KEY `sfz_num` (`sfz_num`) USING BTREE\n" +
            ") ENGINE=InnoDB AUTO_INCREMENT=3542414 DEFAULT CHARSET=utf8 COMMENT='单题分数表（如每科有30道题目的话，那么5万个学生应该有30*5*17=2500万条数据)';";

    private static final String  createTableDpSubjectScore = "CREATE TABLE IF NOT EXISTS `dp_subject_score` (\n" +
            "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
            "  `zkz_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '准考证(12)位',\n" +
            "  `sfz_num` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '身份证',\n" +
            "  `student_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生ID',\n" +
            "  `student_type` tinyint(2) DEFAULT NULL COMMENT '1线上  2线下',\n" +
            "  `pg_type` tinyint(5) DEFAULT NULL COMMENT '试卷类型（1、2、3、4、5、6）',\n" +
            "  `gaige` tinyint(1) DEFAULT NULL,\n" +
            "  `province_id` tinyint(5) DEFAULT NULL COMMENT '省ID',\n" +
            "  `school_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学校ID',\n" +
            "  `banji_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学校名称',\n" +
            "  `subject_id` int(5) DEFAULT NULL COMMENT '科目ID(共17科单科，6科总科)',\n" +
            "  `subject_relation_id` int(5) DEFAULT NULL,\n" +
            "  `mapping_subject_id` int(5) DEFAULT NULL,\n" +
            "  `score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分数',\n" +
            "  `bfb_sheng` double(20,5) DEFAULT NULL,\n" +
            "  `rank_x` int(11) DEFAULT '0',\n" +
            "  `percentage_g` double(6,2) DEFAULT NULL,\n" +
            "  `percentage_s` double(6,2) DEFAULT NULL,\n" +
            "  `percentage_gaige` double(6,2) DEFAULT NULL,\n" +
            "  `rank_p` int(4) DEFAULT NULL COMMENT '省排名',\n" +
            "  `type` int(2) DEFAULT NULL,\n" +
            "  `rank_z` int(11) DEFAULT NULL,\n" +
            "  `standard_score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `grade` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `luofeng_bfb` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '预估原始分排名',\n" +
            "  `standard_bfb` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '预估标准分排名',\n" +
            "  `temp_subject_id` int(15) DEFAULT NULL,\n" +
            "  `is_predict` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '是否是预选组合',\n" +
            "  PRIMARY KEY (`id`) USING BTREE,\n" +
            "  KEY `province_id` (`province_id`,`subject_id`,`standard_score`) USING BTREE,\n" +
            "  KEY `school_id` (`school_id`) USING BTREE,\n" +
            "  KEY `subject_id` (`subject_id`) USING BTREE,\n" +
            "  KEY `percentage_g` (`percentage_g`) USING BTREE,\n" +
            "  KEY `province_id01` (`province_id`) USING BTREE,\n" +
            "  KEY `percentage_s` (`percentage_s`) USING BTREE,\n" +
            "  KEY `subject_relation_id` (`subject_relation_id`) USING BTREE,\n" +
            "  KEY `pg_type` (`pg_type`) USING BTREE\n" +
            ") ENGINE=InnoDB AUTO_INCREMENT=131071 DEFAULT CHARSET=utf8 COMMENT='单科分数表表   （如5万个学生应该有5*17=85万条数据)';";

    private static final String  createTableDpTotalScore = "CREATE TABLE IF NOT EXISTS `dp_total_score` (\n" +
            "  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
            "  `zkz_num` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '准考证(12)位',\n" +
            "  `sfz_num` varchar(23) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '身份证',\n" +
            "  `student_id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学生ID',\n" +
            "  `student_type` tinyint(2) DEFAULT NULL COMMENT '1线上  2线下',\n" +
            "  `pg_type` tinyint(5) DEFAULT NULL COMMENT '试卷类型（1、2、3、4、5、6）',\n" +
            "  `gaige` tinyint(1) DEFAULT NULL,\n" +
            "  `province_id` tinyint(5) DEFAULT NULL COMMENT '省ID',\n" +
            "  `school_id` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '学校ID',\n" +
            "  `banji_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '班级名称',\n" +
            "  `score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '分数',\n" +
            "  `percentage_g` double(128,2) DEFAULT NULL COMMENT '全国百分比',\n" +
            "  `percentage_s` double(128,2) DEFAULT NULL,\n" +
            "  `rank_p` int(11) DEFAULT NULL,\n" +
            "  `rank_x` int(11) DEFAULT NULL,\n" +
            "  `bfb_pm_s` double(5,2) DEFAULT NULL,\n" +
            "  `type` int(2) DEFAULT NULL COMMENT '1 1卷 2 卷',\n" +
            "  `rank_z` int(5) DEFAULT NULL,\n" +
            "  `sfd_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `oss_exist` int(2) DEFAULT '0' COMMENT '0不存在，1存在',\n" +
            "  `percentage_g_1` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `percentage_s_1` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `standard_score` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,\n" +
            "  `mapping_pg_type` tinyint(5) DEFAULT NULL COMMENT '预测考试类型',\n" +
            "  `yuxuan_subject_desc` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '预选组合',\n" +
            "  PRIMARY KEY (`id`) USING BTREE,\n" +
            "  KEY `student_id` (`student_id`) USING BTREE,\n" +
            "  KEY `zkz_num` (`zkz_num`,`pg_type`,`score`,`province_id`) USING BTREE,\n" +
            "  KEY `province_id` (`province_id`) USING BTREE\n" +
            ") ENGINE=InnoDB AUTO_INCREMENT=134049 DEFAULT CHARSET=utf8 COMMENT='总分数表（如5万个学生应该有5万条数据)';";
}
